package org.jeecgframework.web.controller;

import com.alibaba.fastjson.JSONObject;
import net.sf.json.JSONArray;
import org.apache.log4j.Logger;
import org.hibernate.criterion.Restrictions;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.exception.BusinessException;
import org.jeecgframework.core.common.hibernate.qbc.CriteriaQuery;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.ComboTree;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.MyBeanUtils;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.easyui.ComboTreeModel;
import org.jeecgframework.web.cgreport.common.CgReportConstant;
import org.jeecgframework.web.cgreport.entity.core.CgreportConfigHeadEntity;
import org.jeecgframework.web.service.CbsCgreportHeadServiceI;
import org.jeecgframework.web.service.CbsConditionServiceI;
import org.jeecgframework.web.service.CbsCustomizeReportServiceI;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @author onlineGenerator
 * @version V1.0
 * @Title: Controller
 * @Description: cbs_cgreport_head
 * @date 2018-07-25 10:44:43
 */
@Scope("prototype")
@Controller
@RequestMapping("/cbsCgreportHeadController")
public class CbsCgreportHeadController extends BaseController {
    /**
     * Logger for this class
     */
    private static final Logger logger = Logger.getLogger(CbsCgreportHeadController.class);

    @Autowired
    private CbsCgreportHeadServiceI cbsCgreportHeadService;
    @Autowired
    private CbsCustomizeReportServiceI cbsCustomizeReportServiceI;
    @Autowired
    private CbsConditionServiceI cbsConditionServiceI;
    @Autowired
    private SystemService systemService;
    private String message;

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }


    /**
     * cbs_cgreport_head列表 页面跳转
     *
     * @return
     */
    @RequestMapping(params = "cbsCgreportHead")
    public ModelAndView cbsCgreportHead(HttpServletRequest request) {
        return new ModelAndView("com/zybk/cbscgreporthead/cbsCgreportHeadList");
    }

    /**
     * easyui AJAX请求数据
     *
     * @param request
     * @param response
     * @param dataGrid
     * @param
     */

    @RequestMapping(params = "datagrid")
    public void datagrid(CbsCgreportHeadEntity cbsCgreportHead, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
        TSUser user = ResourceUtil.getSessionUserName();
        CriteriaQuery cq = new CriteriaQuery(CbsCgreportHeadEntity.class, dataGrid);
        //查询条件组装器
        org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil.installHql(cq, cbsCgreportHead, request.getParameterMap());
        try {
            //自定义追加查询条件
        } catch (Exception e) {
            throw new BusinessException(e.getMessage());
        }
        //判断角色是否是管理员
        boolean flag = false;
        List<Map<String, Object>> roleList = systemService.findForJdbc("SELECT * FROM t_s_role r LEFT JOIN t_s_role_user u ON r.id = u.roleid where u.userid = '" + user.getId() + "';");
        if (StringUtil.isNotEmpty(roleList)) {
            if (roleList.size() > 0) {
                String num = (String) roleList.get(0).get("rolecode");
                if (num.equals(StaticValue.ROLE_CODE_ADMIN)) {
                    flag = true;  //是管理员
                }
            }
        }

        if (!flag) {
            cq.add(Restrictions.or(Restrictions.eq("property", "1"), Restrictions.eq("createBy", user.getRealName())));//基本过滤条件，只查询未删除的数据
        }
        this.cbsCgreportHeadService.getDataGridReturn(cq, true);
        for (Object object : dataGrid.getResults()) {
            CbsCgreportHeadEntity cbsCgreport = (CbsCgreportHeadEntity) object;
            if (!flag) {  //管理员拥有所有的权限
                if (user.getRealName().equals(cbsCgreport.getCreateBy())) {
                    cbsCgreport.setIsdel("1");
                } else {
                    cbsCgreport.setIsdel("0");
                }
            } else {
                cbsCgreport.setIsdel("1");
            }
        }
        TagUtil.datagrid(response, dataGrid);
    }

    /**
     * cbs_cgreport_head新增页面跳转
     *
     * @return
     */
    @RequestMapping(params = "goAdd")
    public ModelAndView goAdd(CbsCgreportHeadEntity cbsCgreportHead, HttpServletRequest req) {
        if (StringUtil.isNotEmpty(cbsCgreportHead.getId())) {
            cbsCgreportHead = cbsCgreportHeadService.getEntity(CbsCgreportHeadEntity.class, cbsCgreportHead.getId());
            req.setAttribute("cbsCgreportHeadPage", cbsCgreportHead);
        }
        //获取主表和业务表
        //一级菜单
        List<CbsRotaLevelEntity> cbsRotaLevelList = systemService.findByProperty(CbsRotaLevelEntity.class, "status", "Y");
        req.setAttribute("cbsRotaLevelList", cbsRotaLevelList);

        return new ModelAndView("com/zybk/cbscgreporthead/cbsCgreportHead-add");
    }

    /**
     * 通过表名获取字段
     */
    @ResponseBody
    @RequestMapping(params = "getzdNameForTableName")
    public AjaxJson getzdNameForTableName(HttpServletRequest request) {
        AjaxJson ajaxJson = new AjaxJson();
        String[] nameArry = request.getParameterValues("nameArray");    //主业务+关联业务（待选项目展示业务）
        String treeBaList = null;
        if (StringUtil.isNotEmpty(nameArry)) {
            StringBuffer stringBuffer = new StringBuffer();
            for (int i = 0; i < nameArry.length; i++) {
                if (i == 0) {
                    List<Map<String, Object>> rotaName = systemService.findForJdbc("select rota_name from cbs_rota_level where id = '" + nameArry[0] + "'");
                    String name = (String) rotaName.get(0).get("rota_name");
                    if (name.contains("/")) {
                        String[] split = name.split("/");
                        for (int j = 0; j < split.length; j++) {
                            stringBuffer.append(split[j]).append(",");
                        }
                    } else {
                        stringBuffer.append(name).append(",");
                    }
                } else {
                    if (nameArry[i].contains("/")) {
                        String[] split = nameArry[i].split("/");
                        for (int j = 0; j < split.length; j++) {
                            stringBuffer.append(split[j]).append(",");
                        }
                    } else {
                        stringBuffer.append(nameArry[i]).append(",");
                    }
                }
            }
            System.out.println(stringBuffer.toString());

            if (StringUtil.isNotEmpty(stringBuffer.toString())) {
                treeBaList = cbsCgreportHeadService.getzdNameForTableName(stringBuffer.toString().substring(0, stringBuffer.toString().length() - 1));
                ajaxJson.setObj(treeBaList);
            }
        }
        return ajaxJson;
    }

    /**
     * 添加cbs_cgreport_head
     *
     * @param
     * @return
     */
    @RequestMapping(params = "doAdd")
    @ResponseBody
    public AjaxJson doAdd(CbsCgreportHeadEntity cbsCgreportHead, HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        message = "cbs_cgreport_head添加成功";
        try {
            cbsCgreportHeadService.save(cbsCgreportHead);
            systemService.addLog(message, Globals.Log_Type_INSERT, Globals.Log_Leavel_INFO);
        } catch (Exception e) {
            e.printStackTrace();
            message = "cbs_cgreport_head添加失败";
            throw new BusinessException(e.getMessage());
        }
        j.setMsg(message);
        return j;
    }

    /**
     * cbs_cgreport_head编辑页面跳转
     *
     * @return
     */
    @RequestMapping(params = "goUpdate")
    public ModelAndView goUpdate(CbsCgreportHeadEntity cbsCgreportHead, HttpServletRequest req) {
        String id = req.getParameter("id");
        if (StringUtil.isNotEmpty(id)) {
            cbsCgreportHead = cbsCgreportHeadService.getEntity(CbsCgreportHeadEntity.class, id);
            req.setAttribute("cbsCgreportHeadPage", cbsCgreportHead);
        }
        //获取主表和业务表
        //一级菜单
        List<CbsRotaLevelEntity> cbsRotaLevelList = systemService.findByProperty(CbsRotaLevelEntity.class, "status", "Y");
        req.setAttribute("cbsRotaLevelList", cbsRotaLevelList);
        List<Map> maps = JSONObject.parseArray(cbsCgreportHead.getCgrJson(), Map.class);
        req.setAttribute("maps", maps);
        return new ModelAndView("com/zybk/cbscgreporthead/cbsCgreportHead-update");
    }

    /**
     * 更新cbs_cgreport_head
     *
     * @param
     * @return
     */
    @RequestMapping(params = "doUpdate")
    @ResponseBody
    public AjaxJson doUpdate(CbsCgreportHeadEntity cbsCgreportHead, HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        message = "cbs_cgreport_head更新成功";
        CbsCgreportHeadEntity t = cbsCgreportHeadService.get(CbsCgreportHeadEntity.class, cbsCgreportHead.getId());
        try {
            MyBeanUtils.copyBeanNotNull2Bean(cbsCgreportHead, t);
            cbsCgreportHeadService.saveOrUpdate(t);
            systemService.addLog(message, Globals.Log_Type_UPDATE, Globals.Log_Leavel_INFO);
        } catch (Exception e) {
            e.printStackTrace();
            message = "cbs_cgreport_head更新失败";
            throw new BusinessException(e.getMessage());
        }
        j.setMsg(message);
        return j;
    }


    /**
     * 删除cbs_cgreport_head
     *
     * @return
     */
    @RequestMapping(params = "doDel")
    @ResponseBody
    public AjaxJson doDel(CbsCgreportHeadEntity cbsCgreportHead, HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        cbsCgreportHead = systemService.getEntity(CbsCgreportHeadEntity.class, cbsCgreportHead.getId());
        message = "cbs_cgreport_head删除成功";
        try {
            cbsCgreportHeadService.delete(cbsCgreportHead);
            systemService.addLog(message, Globals.Log_Type_DEL, Globals.Log_Leavel_INFO);
        } catch (Exception e) {
            e.printStackTrace();
            message = "cbs_cgreport_head删除失败";
            throw new BusinessException(e.getMessage());
        }
        j.setMsg(message);
        return j;
    }

    /**
     * 批量删除cbs_cgreport_head
     *
     * @return
     */
    @RequestMapping(params = "doBatchDel")
    @ResponseBody
    public AjaxJson doBatchDel(String ids, HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        message = "cbs_cgreport_head删除成功";
        try {
            for (String id : ids.split(",")) {
                CbsCgreportHeadEntity cbsCgreportHead = systemService.getEntity(CbsCgreportHeadEntity.class,
                        id
                );
                cbsCgreportHeadService.delete(cbsCgreportHead);
                systemService.addLog(message, Globals.Log_Type_DEL, Globals.Log_Leavel_INFO);
            }
        } catch (Exception e) {
            e.printStackTrace();
            message = "cbs_cgreport_head删除失败";
            throw new BusinessException(e.getMessage());
        }
        j.setMsg(message);
        return j;
    }

    /**
     * 根据一级id获取二级的内容
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(params = "getRotalevel", method = RequestMethod.POST)
    @ResponseBody
    public AjaxJson getRotalevel(HttpServletRequest request, HttpServletResponse response) {
        AjaxJson ajaxJson = new AjaxJson();
        String levelid = request.getParameter("levelid");
        if (StringUtil.isNotEmpty(levelid)) {
            List<CbsRotaLevelEntity> list = cbsCgreportHeadService.getRotalevels(levelid);
            ajaxJson.setObj(list);
        }
        return ajaxJson;
    }

    /*
     * 判断报表名称是否唯一
     * */
    @RequestMapping(params = "checkName")
    @ResponseBody
    public AjaxJson checkName(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        String name = request.getParameter("name");
        List<CgreportConfigHeadEntity> reportList = systemService.findHql("from CbsCgreportHeadEntity where name='" + name + "'");
        j.setObj(reportList.size());
        return j;
    }

    /**
     * 根据已选项目id，组合自定义条件
     *
     * @return
     */
    @RequestMapping(params = "getCgreportGen")
    @ResponseBody
    public AjaxJson getCgreportGen(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        List<CbsCustomizeReportEntity> cbsCustomizeReports = new ArrayList<>();
        String vkorg = request.getParameter("vkorg");
        String[] tj = vkorg.split(",");
        if (tj.length > 0) {
            for (String s : tj) { //根据字段id获取内容
                CbsCustomizeReportEntity cbsCustomizeReport = systemService.get(CbsCustomizeReportEntity.class, s);
                cbsCustomizeReports.add(cbsCustomizeReport);
            }
        }
        j.setObj(cbsCustomizeReports);
        return j;
    }


    /**
     * 根据id获取内容
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "getTerm")
    @ResponseBody
    public AjaxJson getTerm(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        List<CbsCustomizeReportEntity> cbsCustomizeReports = new ArrayList<>();
        String vkorg = request.getParameter("vkorg");
        String[] tj = vkorg.split(",");
        if (tj.length > 0) {
            for (String s : tj) { //根据字段id获取内容
                CbsCustomizeReportEntity cbsCustomizeReport = systemService.get(CbsCustomizeReportEntity.class, s);
                cbsCustomizeReports.add(cbsCustomizeReport);
            }
        }
        j.setObj(cbsCustomizeReports);
        return j;
    }

    /**
     * 获取查询条件
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "getTermTree")
    @ResponseBody
    public List<ComboTree> getTermTree(HttpServletRequest request) {
        List<ComboTree> comboTrees = new ArrayList<ComboTree>();
        List<CbsCustomizeReportEntity> cbsCustomizeReports = new ArrayList<>();
        String term = request.getParameter("term");
        String checkedValue = request.getParameter("checkedValue");
        CbsConditionEntity cbsCondition = systemService.findUniqueByProperty(CbsConditionEntity.class, "conditioncode", term);
        if (StringUtil.isNotEmpty(cbsCondition)) {
            List<CbsConditionEntity> cbsConditionEntities = cbsConditionServiceI.getTermTree(cbsCondition.getId());
            ComboTreeModel comboTreeModel = new ComboTreeModel("id", "mesChinese", "", "");
            comboTrees = systemService.comboTree(cbsConditionEntities, comboTreeModel, null, false);
        }
        if (StringUtil.isNotEmpty(checkedValue)) {
            for (ComboTree comboTree : comboTrees) {
                if (comboTree.getId().equals(checkedValue)) {
                    comboTree.setChecked(true);
                }
            }
        }
        return comboTrees;
    }

    /**
     * 字典表类型查询数据
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "getfieldNameBycode")
    @ResponseBody
    public AjaxJson getfieldNameBycode(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        String diccode = request.getParameter("fieldCode");
        if (StringUtil.isNotEmpty(diccode)) {
            StringBuilder dicSql = new StringBuilder();
            dicSql.append(" SELECT TYPECODE,TYPENAME FROM");
            dicSql.append(" " + CgReportConstant.SYS_DIC);
            dicSql.append(" " + "WHERE TYPEGROUPID = ");
            dicSql.append(" " + "(SELECT ID FROM " + CgReportConstant.SYS_DICGROUP + " WHERE TYPEGROUPCODE = '" + diccode + "' )");
            List<Map<String, Object>> dicDatas = systemService.findForJdbc(dicSql.toString());
            j.setObj(dicDatas);
        }
        return j;
    }

    /**
     * 获取可做时间差查询的属性
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "getTimeDiff")
    @ResponseBody
    public AjaxJson getTimeDiff(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        String otabName = request.getParameter("otabName"); //主表id
        String ttabName = request.getParameter("ttabName"); //关联表name

        //去除非查询表的，时间查询字段
        List<String> list = new ArrayList<>();
        if (StringUtil.isNotEmpty(otabName)) {
            JSONArray jsonArr = JSONArray.fromObject(otabName);
            CbsRotaLevelEntity cbsRotaLevel = systemService.get(CbsRotaLevelEntity.class, jsonArr.get(0).toString());
            if (StringUtil.isNotEmpty(cbsRotaLevel)) {
                list.add(cbsRotaLevel.getRotaName());
            }
        }
        if (StringUtil.isNotEmpty(ttabName)) {  /*关联表*/
            JSONArray jsonArr = JSONArray.fromObject(ttabName);
            for (Object o : jsonArr) {
                if (StringUtil.isNotEmpty(o)) {
                    if (o.toString().contains("/")) {
                        String[] s = o.toString().split("/");
                        for (String s1 : s) {
                            list.add(s1);
                        }
                    } else {
                        list.add(o.toString());
                    }
                }
            }
        }

        List<CbsCustomizeReportEntity> cbsCustomizeReportEntities = cbsCustomizeReportServiceI.getTimeDiff(list);
        j.setObj(cbsCustomizeReportEntities);
        return j;
    }

    /**
     * 预览
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "getSqlResult")
    public ModelAndView getSqlResult(HttpServletRequest request) {
        try{
            String name = request.getParameter("name"); //报表名称
            String vkorg = request.getParameter("vkorg"); //已选项目对应id
            String otabId = request.getParameter("otabName");  //主业务id
            String ttabName = request.getParameter("ttabName");  //关联业务表名
            String resultObj = request.getParameter("result");  //自定义条件数据库字段信息

            List nameList = new ArrayList();
            List idTypeList = new ArrayList();
            String[] mid = {};
            if (StringUtil.isNotEmpty(vkorg)) {
                mid = vkorg.split(",");
                for (int i = 0; i < mid.length; i++) {
                    String cbscrId = "SELECT id as id,field_type as type,field_chinese AS s FROM cbs_customize_report WHERE id='" + mid[i] + "'";
                    List<Map<String, Object>> map = systemService.findForJdbc(cbscrId);
                    if (!nameList.contains(map.get(0).get("s"))) {
                        nameList.add(map.get(0).get("s"));
                    }
                    idTypeList.add(map.get(0).get("id") + "_" + map.get(0).get("type") + "_" + map.get(0).get("s"));
                }


                Map mapList = cbsCgreportHeadService.getSqlResult(vkorg, otabId, ttabName, resultObj, "1");
                System.out.println("拼接后的sql=" + mapList.get("sql").toString());
                logger.info("拼接后的sql=" + mapList.get("sql").toString());
                List<Map<String, Object>> resultList = systemService.findForJdbc(mapList.get("sql").toString());

                //页面字典表和包含固定内容解析
                for (Map<String, Object> map : resultList) {
                    for (Object o : idTypeList) {
                        String[] s = o.toString().split("_");
                        if (s[1].equals("2")) {  //其他业务关联类型
                            String foreignSql = "select foreignkey_name,foreignkey_tab,foreignkey from cbs_customize_report where id='" + s[0] + "'";
                            List<Map<String, Object>> foreignMap = systemService.findForJdbc(foreignSql);
                            if (foreignMap.size() > 0) {
                                if (StringUtil.isNotEmpty(foreignMap.get(0).get("foreignkey_name")) && StringUtil.isNotEmpty(foreignMap.get(0).get("foreignkey_tab")) && StringUtil.isNotEmpty(foreignMap.get(0).get("foreignkey"))) {
                                    String namekey = foreignMap.get(0).get("foreignkey_name").toString();
                                    String tab = foreignMap.get(0).get("foreignkey_tab").toString();
                                    String key = foreignMap.get(0).get("foreignkey").toString();
                                    if (StringUtil.isNotEmpty(s[2]) && StringUtil.isNotEmpty(map.get(s[2]))) {
                                        String foreignSqls = "select " + namekey + " as name from " + tab + " where " + key + " = '" + map.get(s[2]).toString() + "'";
                                        List<Map<String, Object>> foreignMaps = systemService.findForJdbc(foreignSqls);
                                        if (foreignMaps.size() > 0) {
                                            map.put(s[2], foreignMaps.get(0).get("name"));
                                        } else {
                                            map.put(s[2], "");
                                        }
                                    } else {
                                        map.put(s[2], "");
                                    }
                                }
                            }
                        }
                        if (s[1].equals("3")) {  /*包含固定内容*/
                            /*表id*/
                            String idString = "SELECT fixedcontent as fixedcontent FROM cbs_customize_report WHERE id='" + s[0] + "'";
                            List<Map<String, Object>> fixedcontentMap = systemService.findForJdbc(idString);
                            String fixedcontent = fixedcontentMap.get(0).get("fixedcontent").toString();
                            JSONArray array = JSONArray.fromObject(fixedcontent);
                            for (Object o1 : array) {
                                net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(o1);
                                Iterator it = jsonObj.keys();
                                while (it.hasNext()) {
                                    System.out.println("--" + map.get(s[2].toString()));
                                    String string = (String) it.next();
                                    System.out.println("ss=" + jsonObj.get(string));
                                    if (StringUtil.isNotEmpty(s[2]) && StringUtil.isNotEmpty(map.get(s[2]))) {
                                        if (jsonObj.get(string).equals(map.get(s[2]).toString())) {
                                            map.put(s[2], string);
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                        if (s[1].equals("4")) {   /*字典*/
                            String typeSql = "SELECT\n" +
                                    "\tt_s_type.typename\n" +
                                    "FROM\n" +
                                    "\tcbs_customize_report\n" +
                                    "LEFT JOIN t_s_typegroup ON t_s_typegroup.typegroupcode = cbs_customize_report.dictionary\n" +
                                    "LEFT JOIN t_s_type ON t_s_type.typegroupid = t_s_typegroup.ID\n" +
                                    "WHERE\n" +
                                    "\tcbs_customize_report.id='" + s[0] + "'" +
                                    "AND t_s_type.typecode = '" + map.get(s[2].toString()) + "'";

                            List<Map<String, Object>> typenameMap = systemService.findForJdbc(typeSql);
                            if (typenameMap.size() > 0) {
                                map.put(s[2], typenameMap.get(0).get("typename"));
                            }
                        }
                    }
                }

                request.setAttribute("sqlResultName", nameList);  //table名称
                request.setAttribute("sqlResultlist", resultList); //table内容
                request.setAttribute("sqlResultlistSize", resultList.size()); //table内容
                request.setAttribute("name", name); //报表名称
                request.setAttribute("jsonSql", mapList.get("json").toString());
                SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
                Date date = new Date();
                request.setAttribute("date", sf.format(date)); //报表名称

                TSUser user = ResourceUtil.getSessionUserName();
                /*执行日志*/
                systemService.addLog("执行自定义报表。执行人" + user.getRealName() + "。执行日期:" + sf.format(date) + "。报表名称:" + name, Globals.Log_Leavel_INFO, Globals.log_type_SEND_EMAIL);

            }
            return new ModelAndView("com/zybk/cbscgreporthead/cbsCgreportHead-sqlResultview");
        }catch (Exception e){
            request.setAttribute("type","preview");
            return new ModelAndView("com/zybk/cbscgreporthead/checkSql");
        }
    }

    /**
     * 保存
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "addSqlResult")
    @ResponseBody
    public AjaxJson addSqlResult(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        String vkorg = request.getParameter("vkorg");
        String otabId = request.getParameter("otabName");
        String ttabName = request.getParameter("ttabName");
        String resultObj = request.getParameter("result");
        String reportName = request.getParameter("reportName"); //表名
        String content = request.getParameter("content"); //说明
        String gx = request.getParameter("gx"); //是否共享 1，代表是。0，代表否
        String cgid = request.getParameter("cgid"); //当编辑时，为id。否则为空
        String type = request.getParameter("type"); //save:保存 update：编辑
        Map map = cbsCgreportHeadService.getSqlResult(vkorg, otabId, ttabName, resultObj, "2");
        TSUser user = ResourceUtil.getSessionUserName(); //当前人
        String sql = map.get("sql").toString(); //sql
        String jsonSql = map.get("json").toString(); //拼接的json

        CbsCgreportHeadEntity cbsCgreportHead;
        Short logType;
        //if 更新  else 保存
        if (StringUtil.isNotEmpty(type) && StringUtil.isNotEmpty(cgid) && type.equals("update")) {
            cbsCgreportHead = systemService.get(CbsCgreportHeadEntity.class, cgid);
            logType = Globals.Log_Type_UPDATE;
            message = "更新自定义报表。更新人：" + user.getRealName() + "。更新日期:" + DateUtils.date_sdf.format(new Date()) + "。报表名称:";
            cbsCgreportHead.setUpdateDate(new Date());
            cbsCgreportHead.setUpdateBy(user.getRealName());
        } else {
            cbsCgreportHead = new CbsCgreportHeadEntity();
            logType = Globals.Log_Type_INSERT;
            message = "新建自定义报表。创建人：" + user.getRealName() + "。创建日期:" + DateUtils.date_sdf.format(new Date()) + "。报表名称:";
            cbsCgreportHead.setCreateBy(user.getRealName());
            cbsCgreportHead.setCreateDate(new Date());
        }
        cbsCgreportHead.setCgrSql(sql);
        cbsCgreportHead.setCgrJson(jsonSql);
        cbsCgreportHead.setContent(content);
        cbsCgreportHead.setProperty(gx);
        cbsCgreportHead.setPrimatyRota(otabId);
        cbsCgreportHead.setName(reportName);
        cbsCgreportHead.setLieName(vkorg);
        cbsCgreportHead.setLienkedRota(ttabName);
        cbsCgreportHeadService.saveOrUpdate(cbsCgreportHead);

        /*更新/保存 系统日志*/
        systemService.addLog(message + cbsCgreportHead.getName(), logType, Globals.Log_Leavel_INFO);

        return j;
    }


    /**
     * 执行已有sql
     *
     * @return
     */
    @RequestMapping(params = "showSqlView")
    public ModelAndView showSqlView(HttpServletRequest request) {
        try{
            String id = request.getParameter("id");
            CbsCgreportHeadEntity cbsCgreportHead = cbsCgreportHeadService.get(CbsCgreportHeadEntity.class, id);
            List<Map<String, Object>> resultList = systemService.findForJdbc(cbsCgreportHead.getCgrSql());
            //存放自定义报表查询项目中文名称（数据库字段对应cbs_customize_report.field_chinese）
            List nameList = new ArrayList();
            //存放自定义报表的自定义条件
            List idTypeList = new ArrayList();
            String[] mid = {};
            if (StringUtil.isNotEmpty(cbsCgreportHead.getLieName())) {
                mid = cbsCgreportHead.getLieName().split(",");
                for (int i = 0; i < mid.length; i++) {
                    String cbscrId = "SELECT id as id,field_type as type,field_chinese AS s FROM cbs_customize_report WHERE id='" + mid[i] + "'";
                    List<Map<String, Object>> map = systemService.findForJdbc(cbscrId);
                    if(map.size()>0){
                        if (!nameList.contains(map.get(0).get("s"))) {
                            nameList.add(map.get(0).get("s"));
                        }
                        idTypeList.add(map.get(0).get("id") + "_" + map.get(0).get("type") + "_" + map.get(0).get("s"));
                    }
                }
            }

            //页面字典表和包含固定内容解析
            for (Map<String, Object> map : resultList) {
                for (Object o : idTypeList) {
                    //s[0]：自定义条件id      s[1]：自定义条件类型（1=普通，2=其他业务表ID，3=包含固定内容，4=字典值）    s[2]：自定义条件的查询项目中文名称
                    String[] s = o.toString().split("_");
                    if (s[1].equals("2")) {  //其他业务关联类型
                        String foreignSql = "select foreignkey_name,foreignkey_tab,foreignkey from cbs_customize_report where id='" + s[0] + "'";
                        List<Map<String, Object>> foreignMap = systemService.findForJdbc(foreignSql);
                        if (foreignMap.size() > 0) {
                            if (StringUtil.isNotEmpty(foreignMap.get(0).get("foreignkey_name")) && StringUtil.isNotEmpty(foreignMap.get(0).get("foreignkey_tab")) && StringUtil.isNotEmpty(foreignMap.get(0).get("foreignkey"))) {
                                String namekey = foreignMap.get(0).get("foreignkey_name").toString();
                                String tab = foreignMap.get(0).get("foreignkey_tab").toString();
                                String key = foreignMap.get(0).get("foreignkey").toString();
                                if (StringUtil.isNotEmpty(s[2]) && StringUtil.isNotEmpty(map.get(s[2]))) {
                                    String foreignSqls = "select " + namekey + " as name from " + tab + " where " + key + " = '" + map.get(s[2]).toString() + "'";
                                    List<Map<String, Object>> foreignMaps = systemService.findForJdbc(foreignSqls);
                                    if (foreignMaps.size() > 0) {
                                        map.put(s[2], foreignMaps.get(0).get("name"));
                                    } else {
                                        map.put(s[2], "");
                                    }
                                } else {
                                    map.put(s[2], "");
                                }
                            }
                        }
                    }
                    if (s[1].equals("3")) {  /*包含固定内容*/
                        String idString = "SELECT fixedcontent as fixedcontent FROM cbs_customize_report WHERE id='" + s[0] + "'";
                        List<Map<String, Object>> fixedcontentMap = systemService.findForJdbc(idString);
                        String fixedcontent = fixedcontentMap.get(0).get("fixedcontent").toString();
                        JSONArray array = JSONArray.fromObject(fixedcontent);
                        for (Object o1 : array) {
                            net.sf.json.JSONObject jsonObj = net.sf.json.JSONObject.fromObject(o1);
                            Iterator it = jsonObj.keys();
                            while (it.hasNext()) {
                                String string = (String) it.next();
                                if (StringUtil.isNotEmpty(s[2]) && StringUtil.isNotEmpty(map.get(s[2]))) {
                                    if (jsonObj.get(string).equals(map.get(s[2]).toString())) {
                                        map.put(s[2], string);
                                        break;
                                    }
                                }
                            }
                        }
                    }
                    if (s[1].equals("4")) {   /*字典*/
                        if (StringUtil.isNotEmpty(s[2]) && StringUtil.isNotEmpty(map.get(s[2].toString()))) {
                            String typeSql = "SELECT\n" +
                                    "\tt_s_type.typename\n" +
                                    "FROM\n" +
                                    "\tcbs_customize_report\n" +
                                    "LEFT JOIN t_s_typegroup ON t_s_typegroup.typegroupcode = cbs_customize_report.dictionary\n" +
                                    "LEFT JOIN t_s_type ON t_s_type.typegroupid = t_s_typegroup.ID\n" +
                                    "WHERE\n" +
                                    "\tcbs_customize_report.id='" + s[0] + "'" +
                                    "AND t_s_type.typecode = '" + map.get(s[2].toString()) + "'";

                            List<Map<String, Object>> typenameMap = systemService.findForJdbc(typeSql);
                            if (typenameMap.size() > 0) {
                                map.put(s[2], typenameMap.get(0).get("typename"));
                            }
                        }
                    }
                }
            }

            request.setAttribute("sqlResultName", nameList);  //table名称
            request.setAttribute("sqlResultlist", resultList); //table内容
            request.setAttribute("sqlResultlistSize", resultList.size()); //table内容
            request.setAttribute("name", cbsCgreportHead.getName()); //报表名称
            request.setAttribute("jsonSql", cbsCgreportHead.getCgrJson());

            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = new Date();
            request.setAttribute("date", sf.format(date)); //报表名称


            TSUser user = ResourceUtil.getSessionUserName();
            /*执行日志*/
            systemService.addLog("执行自定义报表。执行人" + user.getRealName() + "。执行日期:" + sf.format(date) + "。报表名称:" + cbsCgreportHead.getName(), Globals.Log_Leavel_INFO, Globals.log_type_SEND_EMAIL);

            return new ModelAndView("com/zybk/cbscgreporthead/cbsCgreportHead-sqlResultview");
        }catch (BadSqlGrammarException e){
            request.setAttribute("type","execute");
            return new ModelAndView("com/zybk/cbscgreporthead/checkSql");
        }

    }


    /**
     * 根据id判断，是否能编辑
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "getResultByid")
    @ResponseBody
    public AjaxJson getResultByid(HttpServletRequest request) {
        AjaxJson ajaxJson = new AjaxJson();
        String id = request.getParameter("id");
        CbsCgreportHeadEntity cbsCgreport = systemService.get(CbsCgreportHeadEntity.class, id);
        String createBy = cbsCgreport.getCreateBy();  //创建人
        TSUser user = ResourceUtil.getSessionUserName();
//判断角色是否是管理员
        boolean flag = false;
        List<Map<String, Object>> roleList = systemService.findForJdbc("SELECT * FROM t_s_role r LEFT JOIN t_s_role_user u ON r.id = u.roleid where u.userid = '" + user.getId() + "';");
        if (StringUtil.isNotEmpty(roleList)) {
            if (roleList.size() > 0) {
                String num = (String) roleList.get(0).get("rolecode");
                if (num.equals(StaticValue.ROLE_CODE_ADMIN)) {
                    flag = true;  //是管理员
                }
            }
        }

        if (!flag) {
            if (user.getRealName().equals(createBy)) {
                ajaxJson.setSuccess(true);
            } else {
                ajaxJson.setSuccess(false);
            }
        } else {
            ajaxJson.setSuccess(true);
        }
        return ajaxJson;
    }

    /**
     * 判断已选项目的表，是否没有选中
     *
     * @param request
     * @return
     */
    @RequestMapping(params = "checkedTabName")
    @ResponseBody
    public AjaxJson checkedTabName(HttpServletRequest request) {
        AjaxJson ajaxJson = new AjaxJson();
        boolean f = true;
        List list = new ArrayList();
        String otabName = request.getParameter("otabName"); //主表
        String ttabName = request.getParameter("ttabName"); //关联表
        String vkorg = request.getParameter("vkorg"); //已选项目

        //去除非查询表的，时间查询字段
        if (StringUtil.isNotEmpty(otabName)) {
            JSONArray jsonArr = JSONArray.fromObject(otabName);
            CbsRotaLevelEntity cbsRotaLevel = systemService.get(CbsRotaLevelEntity.class, jsonArr.get(0).toString());
            if (StringUtil.isNotEmpty(cbsRotaLevel)) {
                list.add(cbsRotaLevel.getRotaName());
            }
        }
        if (StringUtil.isNotEmpty(ttabName)) {  /*关联表*/
            JSONArray jsonArr = JSONArray.fromObject(ttabName);
            for (Object o : jsonArr) {
                if (StringUtil.isNotEmpty(o)) {
                    if (o.toString().contains("/")) {
                        String[] s = o.toString().split("/");
                        for (String s1 : s) {
                            list.add(s1);
                        }
                    } else {
                        list.add(o.toString());
                    }
                }
            }
        }

        String[] mid = {};
        if (StringUtil.isNotEmpty(vkorg)) {
            mid = vkorg.split(",");
            for (int i = 0; i < mid.length; i++) {
                String cbscrId = "SELECT rota_name as s FROM cbs_customize_report WHERE id ='" + mid[i] + "'";
                List<Map<String, Object>> map = systemService.findForJdbc(cbscrId);
                if (map.size() > 0) {
                    if(list.size()>0){
                        StringBuffer s = new StringBuffer();
                        for (Object o : list) {
                            s.append(o);
                            s.append("/");
                        }
                        String mlist = s.toString();
                        if (!mlist.contains(map.get(0).get("s").toString())) {
                            f = false;
                            break;
                        }
                    }

                } else {
                    f = false;
                    break;
                }
            }
        }
        ajaxJson.setSuccess(f);
        return ajaxJson;
    }
}
